﻿﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using transferObjects;
using System.Collections.Generic;

/// <summary>
/// Descripción breve de EmpresaDB
/// </summary>
namespace dao.sqlServer2005
{
    public class FaenaDB
    {
        SqlCommand insertFaena;
        SqlCommand selectTodasFaenas;
        SqlCommand selectFaena;
        SqlCommand selectFaenasEmpresa;
        SqlCommand updateFaena;

        public FaenaDB(SqlConnection connection)
        {
            String query = "";

            query = "INSERT INTO FAENA (NombreFaena,EmpresaID,Inicio,Termino,Estado,NombresSupervisor,ApellidosSupervisor,EmailSupervisor,Tel1Supervisor,Tel2Supervisor,EntregaRegl, Observaciones) " +
                    "VALUES (@NombreFaena,@EmpresaID,@Inicio,@Termino,@Estado,@NombresSupervisor,@ApellidosSupervisor,@EmailSupervisor,@Tel1Supervisor,@Tel2Supervisor,@EntregaRegl,@Observaciones) ";
            insertFaena = new SqlCommand(query, connection);

            

            //query que selecciona todos las faenas 
            query = "SELECT * " +
                    "FROM FAENA";
            selectTodasFaenas = new SqlCommand(query, connection);


            //Seleccionar una faena
            query = "SELECT * FROM FAENA WHERE nombreFaena=@nombreFaena AND empresaID=@empresaID";
            selectFaena = new SqlCommand(query, connection);

            //query que selecciona todos las faenas de una empresa 
            query = "SELECT * FROM FAENA WHERE empresaID= @empresaID";
            selectFaenasEmpresa = new SqlCommand(query, connection);
            
            //Modificar una faena
            query = "UPDATE Faena SET NombreFaena=@NombreFaena, EmpresaID=@EmpresaID, Inicio=@Inicio, Termino=@Termino, Estado=@Estado, NombresSupervisor=@NombresSupervisor, ApellidosSupervisor=@ApellidosSupervisor, EmailSupervisor=@EmailSupervisor, Tel1Supervisor=@Tel1Supervisor, Tel2Supervisor=@Tel2Supervisor, EntregaRegl=@EntregaRegl, Observaciones=@Observaciones " +
                    "WHERE NombreFaena=@NombreFaena AND EmpresaID=@EmpresaID";
            updateFaena = new SqlCommand(query, connection);
            
           
             
        }


        public int agregarFaena(Faena faena, String empresaID)
        {

            try
            {
                insertFaena.Parameters.Add(new SqlParameter("NombreFaena", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("EmpresaID", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("Inicio", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("Termino", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("Estado", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("NombresSupervisor", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("ApellidosSupervisor", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("EmailSupervisor", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("Tel1Supervisor", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("Tel2Supervisor", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("EntregaRegl", SqlDbType.VarChar));
                insertFaena.Parameters.Add(new SqlParameter("Observaciones", SqlDbType.VarChar));
                insertFaena.Parameters[0].Value = faena.NombreFaena;
                insertFaena.Parameters[1].Value = empresaID;
                insertFaena.Parameters[2].Value = faena.Inicio;
                insertFaena.Parameters[3].Value = faena.Termino;
                insertFaena.Parameters[4].Value = faena.Estado;
                insertFaena.Parameters[5].Value = faena.NombresSupervisor;
                insertFaena.Parameters[6].Value = faena.ApellidosSupervisor;
                insertFaena.Parameters[7].Value = faena.EmailSupervisor;
                insertFaena.Parameters[8].Value = faena.Tel1Supervisor;
                insertFaena.Parameters[9].Value = faena.Tel2Supervisor;
                insertFaena.Parameters[10].Value = faena.EntregaRegl;
                insertFaena.Parameters[11].Value = faena.Observaciones;


                int res = insertFaena.ExecuteNonQuery();

                return res;
            }
            catch (Exception e)
            {
                throw new Exception("Error en dao.sqlServer2005.EmpresaDB metodo agregar Faena \n\n" + e.Message);
            }
        }

        //obtener una Faena
        public Faena obtenerFaena(String nombreFaena, String empresaID)
        {
            try
            {
                selectFaena.Parameters.Add("nombreFaena", SqlDbType.VarChar);
                selectFaena.Parameters.Add("empresaID", SqlDbType.VarChar);
                selectFaena.Parameters[0].Value = nombreFaena;
                selectFaena.Parameters[1].Value = empresaID;

                SqlDataReader reader = selectFaena.ExecuteReader();
                Faena faena = new Faena();
                EmpresaDB empresaDB;

                if (reader.Read())
                {
                    //instancia para obtener el nombre de fantasia de la empresa
                    SqlConnection conn = ConnectionManager.getSqlConnection();
                    conn.Open();
                    empresaDB = new EmpresaDB(conn);

                    faena = new Faena();
                    faena.EmpresaID = reader.GetString(1);
                    faena.NombreFantasia = empresaDB.obtenerNombreFantasia(faena.EmpresaID);
                    faena.NombreFaena = reader.GetString(0);
                    faena.Inicio = reader.GetString(2);
                    faena.Termino = reader.GetString(3);
                    faena.Estado = reader.GetString(4);
                    faena.NombresSupervisor = reader.GetString(5);
                    faena.ApellidosSupervisor = reader.GetString(6);
                    faena.EmailSupervisor = reader.GetString(7);
                    faena.Tel1Supervisor = reader.GetString(8);
                    faena.Tel2Supervisor = reader.GetString(9);
                    faena.EntregaRegl = reader.GetString(10);
                    faena.Observaciones = reader.GetString(11);
                    conn.Close();
                }
                return faena;
            }

            catch (Exception ex)
            {
                throw new Exception("Error en dao.sqlServer2005.FaenaDB, método obtener faena \n\nDetalle error:" + ex.Message);
            }
        }

        //Modificar una Faena
        public int modificarFaena(Faena faena, String empresaID)
        {
            try
            {
                updateFaena.Parameters.Add(new SqlParameter("NombreFaena", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("EmpresaID", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("Inicio", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("Termino", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("Estado", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("NombresSupervisor", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("ApellidosSupervisor", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("EmailSupervisor", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("Tel1Supervisor", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("Tel2Supervisor", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("EntregaRegl", SqlDbType.VarChar));
                updateFaena.Parameters.Add(new SqlParameter("Observaciones", SqlDbType.VarChar));
                updateFaena.Parameters[0].Value = faena.NombreFaena;
                updateFaena.Parameters[1].Value = empresaID;
                updateFaena.Parameters[2].Value = faena.Inicio;
                updateFaena.Parameters[3].Value = faena.Termino;
                updateFaena.Parameters[4].Value = faena.Estado;
                updateFaena.Parameters[5].Value = faena.NombresSupervisor;
                updateFaena.Parameters[6].Value = faena.ApellidosSupervisor;
                updateFaena.Parameters[7].Value = faena.EmailSupervisor;
                updateFaena.Parameters[8].Value = faena.Tel1Supervisor;
                updateFaena.Parameters[9].Value = faena.Tel2Supervisor;
                updateFaena.Parameters[10].Value = faena.EntregaRegl;
                updateFaena.Parameters[11].Value = faena.Observaciones;

                int res = updateFaena.ExecuteNonQuery();

                return res;

            }
            catch (Exception ex)
            {
                throw new Exception("Error en dao.sqlServer2005.DesperfectoDB metodo actualizarDesperfecto \n\n" + ex.Message);
            }
        }



        //obtener todas las empresas de la bd
        public List<Faena> obtenerTodasFaenas()
        {
            try
            {
                List<Faena> faenas = new List<Faena>();
                SqlDataReader reader = selectTodasFaenas.ExecuteReader();
                Faena faena;

                EmpresaDB empresaDB;

                while (reader.Read())
                {
                    //instancia para obtener el nombre de fantasia de la empresa
                    SqlConnection conn = ConnectionManager.getSqlConnection();
                    conn.Open();
                    empresaDB = new EmpresaDB(conn);

                    faena = new Faena();
                    faena.EmpresaID = reader.GetString(1);
                    faena.NombreFantasia = empresaDB.obtenerNombreFantasia(faena.EmpresaID);
                    faena.NombreFaena = reader.GetString(0);
                    faena.Inicio = reader.GetString(2);
                    faena.Termino = reader.GetString(3);
                    faena.Estado = reader.GetString(4);
                    faena.NombresSupervisor = reader.GetString(5);
                    faena.ApellidosSupervisor = reader.GetString(6);
                    faena.EmailSupervisor = reader.GetString(7);
                    faena.Tel1Supervisor = reader.GetString(8);
                    faena.Tel2Supervisor = reader.GetString(9);
                    faena.EntregaRegl = reader.GetString(10);
                    faena.Supervisor = reader.GetString(5) + " " + reader.GetString(6);
                    faenas.Add(faena);
                    conn.Close();
                }
                
                return faenas;
            }
            catch (Exception e)
            {
                throw new Exception("Error en dao.sqlServer2005.FaenaDB, método obtenerTodasFaenas \n\nDetalle error:" + e.Message);
            }

        }

        //obtener todas las Faenas de una empresa
        public List<Faena> obtenerFaenasEmpresa(String empresaID)
        {
            try
            {
                List<Faena> faenas = new List<Faena>();
                selectFaenasEmpresa.Parameters.Add("empresaID", SqlDbType.VarChar);
                selectFaenasEmpresa.Parameters[0].Value = empresaID;

                SqlDataReader reader = selectFaenasEmpresa.ExecuteReader();
                Faena faena;

                while (reader.Read())
                {
                   

                    faena = new Faena();
                    faena.NombreFaena = reader.GetString(0);

                    faenas.Add(faena);
                   
                }

                return faenas;
            }
            catch (Exception e)
            {
                throw new Exception("Error en dao.sqlServer2005.FaenaDB, método obtenerTodasFaenas \n\nDetalle error:" + e.Message);
            }

        }
    }
}

